﻿Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Imports Microsoft.VisualBasic
Imports System.Xml
Imports DocumentFormat.OpenXml.Drawing.Spreadsheet

'Namespace Export.Excel

'    Public Class ExcelXml

'        Private Doc As New XmlDocument()

'        Public Sub OpenDoc(ByVal path As String)
'            Doc.Load(path)
'        End Sub

'        Public Sub SaveDoc(ByVal path As String)
'            Doc.Save(path)
'        End Sub

'        Public Function GetExcelXmlSheet(ByVal name As String) As ExcelXmlSheet

'            Return New ExcelXmlSheet(Doc.GetElementsByTagName("Worksheet").Item(name))

'        End Function

'        Public Function GetExcelXmlSheet(ByVal i As Integer) As ExcelXmlSheet

'            Return New ExcelXmlSheet(Doc.GetElementsByTagName("Worksheet").Item(i))

'        End Function

'    End Class
'    Public Class ExcelXmlSheet

'        Private Sheet As XmlNode

'        Private UltimaRigaxml As XmlNode
'        Private UltimaColonnaxml As XmlNode
'        Private UltimaRiga As Integer = 1
'        Private UltimaColonna As Integer = 1
'        Private temptempo As Long = 0

'        'Public Property maxRiga As Integer = 1
'        'Public Property maxcolonna As Integer = 1
'        Public Property tottempo As Long = 0

'        Public Sub New(ByVal s As XmlNode)

'            Sheet = s

'            'rl = Sheet.OwnerDocument.GetElementsByTagName("Row") ' t.ChildNodes

'        End Sub


'        Public Property Cell(ByVal r As Integer, ByVal c As Integer) As Object
'            Get
'                Return GetSetCell(r, c, Nothing)
'            End Get
'            Set(ByVal val As Object)
'                GetSetCell(r, c, val)
'            End Set
'        End Property

'        Private ur As XmlNode

'        Public Sub PasteData(ByVal Row As Integer, ByVal Column As Integer, dt As DataTable, ParamArray DataColumns() As String)

'            Dim CurrRow = Row
'            Dim CurrCol = Column

'            For Each Col In DataColumns
'                CurrRow = Row
'                For Each dr In dt.Rows
'                    Cell(CurrRow, CurrCol) = dr(Col)
'                    CurrRow += 1
'                Next

'                CurrCol += 1
'            Next

'        End Sub


'        Private Function GetSetCell(ByVal Row As Integer, ByVal Column As Integer, ByVal Value As Object) As Object
'            Dim t As XmlNode = Sheet.FirstChild
'            Dim r As XmlNode
'            Dim rig As Integer, col As Integer
'            Dim a As XmlAttribute
'            Dim datoxml As XmlElement


'            If CInt(t.Attributes("ss:ExpandedRowCount").Value) < Row Then
'                t.Attributes("ss:ExpandedRowCount").InnerText = Row.ToString
'            End If
'            If CInt(t.Attributes("ss:ExpandedColumnCount").Value) < Column Then
'                t.Attributes("ss:ExpandedColumnCount").InnerText = Column.ToString
'            End If

'            temptempo = Now.Ticks

'            If Row < UltimaRiga Or UltimaRigaxml Is Nothing Then
'                UltimaRiga = 1
'                UltimaRigaxml = t.FirstChild
'                UltimaColonna = 1
'                UltimaColonnaxml = UltimaRigaxml.FirstChild
'            End If

'            '-------- cerca riga ------------

'            rig = UltimaRiga - 1

'            Do Until UltimaRigaxml Is Nothing
'                If UltimaRigaxml.Name = "Row" Then
'                    rig += 1
'                    If UltimaRigaxml.Attributes("ss:Index") IsNot Nothing Then
'                        'If rl(ct).Attributes.Count <> 0 Then
'                        rig = CInt(UltimaRigaxml.Attributes("ss:Index").InnerText)
'                    End If

'                    If rig = Row Then
'                        Exit Do
'                    End If
'                    If rig > Row Then
'                        r = t.OwnerDocument.CreateElement("Row", "urn:schemas-microsoft-com:office:spreadsheet")
'                        t.InsertBefore(r, UltimaRigaxml)
'                        UltimaRigaxml = r
'                        Exit Do
'                    End If

'                End If
'                UltimaRigaxml = UltimaRigaxml.NextSibling
'            Loop

'            If UltimaRigaxml Is Nothing Then
'                UltimaRigaxml = t.OwnerDocument.CreateElement("Row", "urn:schemas-microsoft-com:office:spreadsheet")
'                t.AppendChild(UltimaRigaxml)
'            End If

'            tottempo += Now.Ticks - temptempo

'            a = t.OwnerDocument.CreateAttribute("ss", "Index", "urn:schemas-microsoft-com:office:spreadsheet")
'            a.InnerText = Row.ToString
'            UltimaRigaxml.Attributes.Append(a)


'            '-------- cerca colonna ---------
'            UltimaColonna = 1
'            UltimaColonnaxml = UltimaRigaxml.FirstChild
'            'If colonna < UltimaColonna Or UltimaColonnaxml Is Nothing Then
'            '    UltimaColonna = 1
'            '    UltimaColonnaxml = UltimaRigaxml.FirstChild
'            'End If


'            col = UltimaColonna - 1

'            Do Until UltimaColonnaxml Is Nothing
'                col += 1
'                If UltimaColonnaxml.Attributes("ss:Index") IsNot Nothing Then
'                    col = CInt(UltimaColonnaxml.Attributes("ss:Index").InnerText)
'                End If

'                If col = Column Then
'                    Exit Do
'                End If
'                If col > Column Then
'                    r = t.OwnerDocument.CreateElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet")
'                    UltimaRigaxml.InsertBefore(r, UltimaColonnaxml)
'                    UltimaColonnaxml = r
'                    Exit Do
'                End If

'                UltimaColonnaxml = UltimaColonnaxml.NextSibling
'            Loop

'            If UltimaColonnaxml Is Nothing Then
'                UltimaColonnaxml = t.OwnerDocument.CreateElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet")
'                UltimaRigaxml.AppendChild(UltimaColonnaxml)
'            End If

'            a = t.OwnerDocument.CreateAttribute("ss", "Index", "urn:schemas-microsoft-com:office:spreadsheet")
'            a.InnerText = Column.ToString
'            UltimaColonnaxml.Attributes.Append(a)


'            If Not UltimaColonnaxml.HasChildNodes Then
'                datoxml = t.OwnerDocument.CreateElement("Data", "urn:schemas-microsoft-com:office:spreadsheet")
'                a = t.OwnerDocument.CreateAttribute("ss", "Type", "urn:schemas-microsoft-com:office:spreadsheet")
'                datoxml.Attributes.Append(a)
'                UltimaColonnaxml.AppendChild(datoxml)
'            Else
'                datoxml = UltimaColonnaxml.FirstChild
'                a = UltimaColonnaxml.FirstChild.Attributes("ss:Type")
'            End If

'            Dim ValueReturn As Object = Nothing
'            If Value Is Nothing Then
'                Select Case a.InnerText.ToUpper
'                    Case "NUMBER"
'                        ValueReturn = Utils.Conv.ToDouble(datoxml.InnerText)
'                    Case "STRING"
'                        ValueReturn = datoxml.InnerText
'                End Select

'            Else
'                If IsNumeric(Value) Then
'                    datoxml.InnerText = UI.Print.NumStr(Value)
'                    a.InnerText = "Number"
'                Else
'                    datoxml.InnerText = Value
'                    a.InnerText = "String"
'                End If

'            End If



'            UltimaRiga = Row
'            UltimaColonna = Column

'            Return ValueReturn

'        End Function



'    End Class
'    Public Class Functions

'        Public Shared Function ToHtml(ByVal html As String) As String
'            Dim pagExcel As String = html

'            '<\/?tag[^>]*?>
'            '<script[^>]*?>[\s\S]*?<\/script>

'            pagExcel = Regex.Replace(pagExcel, "<\/?tag[^>]*?>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = Regex.Replace(pagExcel, "<\/?img[^>]*?>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = Regex.Replace(pagExcel, "<\/?a[^>]*?>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = Regex.Replace(pagExcel, "<\/?input[^>]*?>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = Regex.Replace(pagExcel, "<\/?span[^>]*?>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = Regex.Replace(pagExcel, "<\/?link[^>]*?>", String.Empty, RegexOptions.IgnoreCase)

'            pagExcel = Regex.Replace(pagExcel, "<script[^>]*?>[\s\S]*?<\/script>", String.Empty, RegexOptions.IgnoreCase)
'            pagExcel = pagExcel.Replace(" tablesorter", "")

'            'pagExcel = Regex.Replace(pagExcel, "<img[^>]*>", String.Empty, RegexOptions.IgnoreCase)
'            ''pagExcel = Regex.Replace(pagExcel, "<a href[^>]*>", String.Empty, RegexOptions.IgnoreCase)
'            'pagExcel = Regex.Replace(pagExcel, "<a [^>]*>", String.Empty, RegexOptions.IgnoreCase)
'            'pagExcel = Regex.Replace(pagExcel, "</a>", String.Empty, RegexOptions.IgnoreCase)
'            'pagExcel = Regex.Replace(pagExcel, "<input[^>]*>", String.Empty, RegexOptions.IgnoreCase)
'            'pagExcel = Regex.Replace(pagExcel, "<span[^>]*>", String.Empty, RegexOptions.IgnoreCase)
'            'pagExcel = pagExcel.Replace("</span>", String.Empty)
'            'pagExcel = TogliJS(pagExcel)

'            Return pagExcel

'        End Function



'    End Class

'End Namespace

Namespace Export.Excel

    Public Class ExcelXml

        Private Doc As SpreadsheetDocument
        'Dim stream As IO.Stream
        Public Sub OpenDoc(ByVal path As String)

            'stream = IO.File.Open(path, IO.FileMode.Open) ', FileMode.Open)

            'Doc = SpreadsheetDocument.Open(stream, True)



            Doc = SpreadsheetDocument.Open(path, True)

            Doc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = True
            Doc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = True

        End Sub

        'Public Function read() As Byte()
        '    Dim buuf(stream.Length - 1) As Byte
        '    stream.Read(buuf, 0, stream.Length)

        '    Return buuf

        'End Function


        Public Sub Close()
            Doc.Close()
            'stream.Close()
        End Sub


        'Public Function GetExcelXmlSheet(ByVal name As String) As ExcelXmlSheet

        '    Dim theSheet As Sheet = Doc.WorkbookPart.Workbook.Descendants(Of Sheet)().
        '     Where(Function(s) s.Name = name).FirstOrDefault()




        '    'Return New ExcelXmlSheet(theSheet.Elements(Of SheetData)().First())

        '    Return New ExcelXmlSheet(Doc.WorkbookPart.WorksheetParts.First.Worksheet.Elements(Of SheetData).First)

        '    'Return New ExcelXmlSheet(Doc.GetElementsByTagName("Worksheet").Item(name))

        'End Function

        Public Function GetExcelXmlSheet(ByVal i As Integer) As ExcelXmlSheet

            Dim sss = Doc.WorkbookPart.Workbook.Descendants(Of Sheet).ToList(i)

            Dim wsPart As WorksheetPart = CType(Doc.WorkbookPart.GetPartById(sss.Id), WorksheetPart)

            Return New ExcelXmlSheet(wsPart)


            'Return New ExcelXmlSheet(wsPart.Worksheet.GetFirstChild(Of SheetData)())

            'Return New ExcelXmlSheet(Doc.WorkbookPart.WorksheetParts.ToList(i).Worksheet.Elements(Of SheetData).First)

            '            Return New ExcelXmlSheet(Doc.GetElementsByTagName("Worksheet").Item(i))

        End Function

        'Public Function image()

        '    Dim sss = Doc.WorkbookPart.Workbook.Descendants(Of Sheet).ToList(0)

        '    Dim wsPart As WorksheetPart = CType(Doc.WorkbookPart.GetPartById(sss.Id), WorksheetPart)

        '    Dim link = wsPart.DrawingsPart.ExternalRelationships.FirstOrDefault

        '    Dim id = link.Id

        '    wsPart.DrawingsPart.DeleteExternalRelationship(id)

        '    'wsPart.DrawingsPart.ExternalRelation

        '    'link.Uri = New System.Uri("http://a0.twimg.com/profile_images/2595434358/wi1lhtl8gcy5c2oca4ky_normal.jpeg", System.UriKind.Absolute)

        '    wsPart.DrawingsPart.AddExternalRelationship(
        '        "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image",
        '        New System.Uri("http://a0.twimg.com/profile_images/3175503796/5c2fb4476bd52beaf77b11aa0beab0b6_normal.png", System.UriKind.Absolute),
        '        id
        '    )


        'End Function




    End Class
    Public Class ExcelXmlSheet

        Private Sheet As WorksheetPart


        Private SheetData As SheetData


        Public Sub Save()

        End Sub
        Public Sub New(ByVal s As WorksheetPart)

            Sheet = s

            SheetData = Sheet.Worksheet.GetFirstChild(Of SheetData)()
            'rl = Sheet.OwnerDocument.GetElementsByTagName("Row") ' t.ChildNodes

        End Sub


        Public Property Cell(ByVal r As Integer, ByVal c As String) As Object
            Get
                Return GetSetCell(r, c, Nothing)
            End Get
            Set(ByVal val As Object)
                GetSetCell(r, c, val)
            End Set
        End Property

        Private ur As XmlNode

        'Public Sub PasteData(ByVal Row As Integer, ByVal Column As String, dt As DataTable, ParamArray DataColumns() As String)

        '    Dim CurrRow = Row
        '    Dim CurrCol = Column

        '    For Each Col In DataColumns
        '        CurrRow = Row
        '        For Each dr In dt.Rows
        '            Cell(CurrRow, CurrCol) = dr(Col)
        '            CurrRow += 1
        '        Next

        '        CurrCol = ColIntToText(Me.ColTextToInt(CurrCol) + 1)
        '    Next

        'End Sub
        Public Sub PasteData(ByVal Row As Integer, ByVal Column As String, dt As Object, ParamArray DataColumns() As String)

            Dim CurrRow = Row
            Dim CurrCol = Column

            For Each Col In DataColumns
                CurrRow = Row
                For Each dr In dt
                    Cell(CurrRow, CurrCol) = DataBinder.Eval(dr, Col)
                    CurrRow += 1
                Next

                CurrCol = ColIntToText(Me.ColTextToInt(CurrCol) + 1)
            Next

        End Sub
        Public Sub PasteDataImage(ByVal Row As Integer, ByVal Column As String, dt As DataTable, ParamArray DataColumns() As String)

            Dim CurrRow = Row
            Dim CurrCol = Column

            For Each dr In dt.Rows
                SetImage(CurrRow, CurrCol, dr(DataColumns(0)))
                CurrRow += 1
            Next


        End Sub


        Public Sub SetImage(ByVal Row As Integer, ByVal Column As String, Url As String)

            Dim RowIndex = Row - 1
            Dim ColIndex = Me.ColTextToInt(Column) - 1


            Dim reflinkid = "Ext_" & Row.ToString & "_" & Column

            Me.Sheet.DrawingsPart.AddExternalRelationship(
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image",
                New System.Uri(Url, System.UriKind.Absolute),
                reflinkid
            )

            Dim anc = Me.Sheet.DrawingsPart.WorksheetDrawing.Elements(Of TwoCellAnchor).Where(Function(o) o.FromMarker.ColumnId.Text = ColIndex And o.FromMarker.RowId.Text = RowIndex).FirstOrDefault

            Dim pic = anc.GetFirstChild(Of DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture)()

            pic.BlipFill.Blip.Link.Value = reflinkid

            'Dim reflinkid = pic.BlipFill.Blip.Link.Value

            'Me.Sheet.DrawingsPart.DeleteExternalRelationship(reflinkid)




        End Sub


        Private Function GetSetCell(ByVal Row As Integer, ByVal Column As String, ByVal Value As Object) As Object
            'Dim worksheet As Worksheet = WorksheetPart.Worksheet

            Dim cellReference As String = (Column + Row.ToString())

            ' If the worksheet does not contain a row with the specified row index, insert one.
            Dim rowdata As Row
            If (SheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = Row).Count() <> 0) Then
                rowdata = SheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = Row).First()
            Else

                Dim refrow As Row = Nothing
                For Each r In SheetData.Descendants(Of Row)()
                    If r.RowIndex.Value > Row Then
                        refrow = r
                    End If
                Next

                rowdata = New Row()
                rowdata.RowIndex = Row
                'Sheet.Append(rowdata)
                SheetData.InsertBefore(rowdata, refrow)

            End If

            Dim newCell As Cell

            ' If there is not a cell with the specified column name, insert one.  
            If (rowdata.Elements(Of Cell).Where(Function(c) c.CellReference.Value = Column + Row.ToString()).Count() > 0) Then
                newCell = rowdata.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
            Else
                ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Dim refCell As Cell = Nothing
                For Each cell As Cell In rowdata.Elements(Of Cell)()
                    If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                        refCell = cell
                        Exit For
                    End If
                Next

                newCell = New Cell
                newCell.CellReference = cellReference

                rowdata.InsertBefore(newCell, refCell)
                'Worksheet.Save()

                'Return newCell
            End If


            If Value IsNot Nothing Then
                Dim StrValue As String
                Select Case Value.GetType
                    Case GetType(String)
                        newCell.DataType = New EnumValue(Of CellValues)(CellValues.String) '( Of CellValues)(CellValues.SharedString)
                        StrValue = Value.ToString

                    Case GetType(Boolean)
                        newCell.DataType = New EnumValue(Of CellValues)(CellValues.Boolean)
                        StrValue = Value.ToString

                    Case GetType(Date)
                        newCell.DataType = New EnumValue(Of CellValues)(CellValues.Date)
                        StrValue = Microsoft.VisualBasic.Format(Value, "yyyy-MM-dd")
                        'StrValue = Value.ToOADate.ToString

                    Case GetType(MySql.Data.Types.MySqlDateTime)
                        newCell.DataType = New EnumValue(Of CellValues)(CellValues.Date)
                        StrValue = Microsoft.VisualBasic.Format(Value.value, "yyyy-MM-dd")

                    Case Else
                        newCell.DataType = New EnumValue(Of CellValues)(CellValues.Number) '( Of CellValues)(CellValues.SharedString)
                        StrValue = DBase.Conv.DoubleToDB(Value)

                End Select

                newCell.CellValue = New CellValue(StrValue)


                'newCell.CellValue

                Return Nothing
            Else
                Return newCell.CellValue.Text
            End If
            ' Set the value of cell A1.


        End Function
        Function ColTextToInt(ByVal col As String) As Long
            Dim c1 As String, c2 As String
            col = UCase(col) 'Make sure we are dealing with "A", not with "a"
            If Len(col) = 1 Then  'if "A" to "Z" is given, there is just one letter to decode
                ColTextToInt = Asc(col) - Asc("A") + 1
            ElseIf Len(col) = 2 Then
                c1 = Left(col, 1)  ' two letter columns: split to left and right letter
                c2 = Right(col, 1)
                ' calculate the column indexes from both letters  
                ColTextToInt = (Asc(c1) - Asc("A") + 1) * 26 + (Asc(c2) - Asc("A") + 1)
            Else
                ColTextToInt = 0
            End If
        End Function

        Function ColIntToText(ByVal col As Long) As String
            Dim i1 As Long, i2 As Long
            i1 = (col - 1) \ 26   ' col - 1 =i1*26+i2 : this calculates i1 and i2 from col 
            i2 = (col - 1) Mod 26
            ColIntToText = Chr(Asc("A") + i2)  ' if i1 is 0, this is the column from "A" to "Z"
            If i1 > 0 Then 'in this case, i1 represents the first letter of the two-letter columns
                ColIntToText = Chr(Asc("A") + i1 - 1) & ColIntToText ' add the first letter to the result
            End If
        End Function



    End Class

End Namespace
